CREATE OR REPLACE PROCEDURE Trf_Account_Insert
	   (
 	   CLT_NAME IN VARCHAR,
	   CLT_PASSPORT IN NUMBER
	   )
AS
  	  OLD_ID NUMBER; 
	  MIN_FREE_ACCOUNT NUMBER;
	  MAX_ACCOUNT NUMBER;
	  CURSOR FREE_ACC_CURSOR IS
	  		 SELECT ACCOUNT_ID 
			 FROM TRF_ACCOUNT
			 WHERE STATUS='-';
BEGIN 
	  --MAX EXISTING NUMBER
	  SELECT NVL(MAX(ACCOUNT_ID),0)INTO MAX_ACCOUNT FROM TRF_ACCOUNT;
	  --OWNER`S ID (ZERO IF NEW CLIENT)
	  SELECT NVL(MAX(CLIENT_ID),0) INTO OLD_ID FROM TRF_CLIENT
	  		 WHERE CLIENT_NAME = CLT_NAME AND PASSPORT_NUMBER = CLT_PASSPORT;
	  --NEW CLIENT
	  IF(OLD_ID = 0)THEN
	     BEGIN
		    INSERT INTO TRF_CLIENT VALUES(CLT_ID.NEXTVAL,CLT_NAME,CLT_PASSPORT);
			SELECT CLT_ID.CURRVAL INTO OLD_ID FROM DUAL;
		 END;
	  END IF;
		  
	  IF(MAX_ACCOUNT>9999998)THEN
	     --IF ALL ACCOUNTS ARE USED
		 BEGIN
		    --MIN FREE ACCOUNT
		    SELECT MIN(ACCOUNT_ID) INTO MIN_FREE_ACCOUNT FROM TRF_FREE_ACCOUNTS;
			IF(MIN_FREE_ACCOUNT IS NOT NULL)THEN
			   BEGIN
			      UPDATE TRF_ACCOUNT
				  SET 
				  CLIENT_ID = OLD_ID, STATUS = '+'
				  WHERE ACCOUNT_ID = MIN_FREE_ACCOUNT;
				  DELETE FROM TRF_FREE_ACCOUNTS WHERE ACCOUNT_ID = MIN_FREE_ACCOUNT;
			   END;
			ELSE
			   --IF NOT EXISTS FREE ACCOUNT, MAKE DISABLE ACCOUNTS FREE
			   BEGIN
			      FOR FREE_ACC IN FREE_ACC_CURSOR
				     LOOP
					    INSERT INTO TRF_FREE_ACCOUNTS VALUES(FREE_ACC.ACCOUNT_ID);
					 END LOOP;
			      DBMS_OUTPUT.PUT_LINE('DISABLE ACCOUNTS BECOME FREE');
				  SELECT MIN(ACCOUNT_ID) INTO MIN_FREE_ACCOUNT FROM TRF_FREE_ACCOUNTS;
				  --INSERT CLIENT AND ACCOUNT
				  IF(MIN_FREE_ACCOUNT IS NOT NULL)THEN
			   	     BEGIN
			            UPDATE TRF_ACCOUNT
						SET 
						   CLIENT_ID = OLD_ID, STATUS = '+'
						WHERE ACCOUNT_ID = MIN_FREE_ACCOUNT;
						DELETE FROM TRF_FREE_ACCOUNTS WHERE ACCOUNT_ID = MIN_FREE_ACCOUNT;
			         END;
			      ELSE
				     --ALL ACCOUNTS USED
			         BEGIN
			            DBMS_OUTPUT.PUT_LINE('NO FREE ACCOUNTS');
			         END;
			      END IF;
			   END;
		    END IF;
		 END;	
	  ELSE
	     BEGIN
	        INSERT INTO TRF_ACCOUNT(ACCOUNT_ID,CLIENT_ID,STATUS) VALUES(ACC_ID.NEXTVAL,OLD_ID,'+');
		 END;	
      END IF;		 
END;
/



